{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "导入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "male=pd.read_excel(r'C:\\Users\\61402\\OneDrive - UNSW\\Desktop\\资料\\拉钩\\阶段五 python\\模块四pandas\\Pandas数据分析库\\18级高一体测成绩汇总.xls'\n",
    ")\n",
    "female=pd.read_excel(r'C:\\Users\\61402\\OneDrive - UNSW\\Desktop\\资料\\拉钩\\阶段五 python\\模块四pandas\\Pandas数据分析库\\18级高一体测成绩汇总.xls',sheet_name=1)\n",
    "PE=pd.read_excel(r'C:\\Users\\61402\\OneDrive - UNSW\\Desktop\\资料\\拉钩\\阶段五 python\\模块四pandas\\Pandas数据分析库\\体侧成绩评分表.xls',header=[0,1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "男子表数据类型转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "male['男1000米跑']=male['男1000米跑'].str.replace(\"'\",'.').astype(float)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "评分表男女数据转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\61402\\anaconda3\\lib\\site-packages\\pandas\\core\\series.py:4563: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  return super().replace(\n"
     ]
    }
   ],
   "source": [
    "PEM=PE.loc[:,'男1000米跑']\n",
    "PEM.loc[:,'成绩'].replace({\"'\":\".\",'\"':''},regex=True,inplace=True)\n",
    "PEM.astype(float)\n",
    "PE['男1000米跑']=PEM\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "PEF=PE.loc[:,'女800米跑']\n",
    "PEF.loc[:,'成绩'].replace({\"'\":\".\",'\"':''},regex=True,inplace=True)\n",
    "PEF.astype(float)\n",
    "PE['女800米跑']=PEF"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "把所有数据类型转化成float"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "PE=pd.DataFrame(PE,dtype=np.float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "male['男50米跑']=male['男50米跑'].astype(float)\n",
    "male['男跳远']=male['男跳远'].astype(float)\n",
    "male['男体前屈']=male['男体前屈'].astype(float)\n",
    "male['男引体']=male['男引体'].astype(float)\n",
    "male['男肺活量']=male['男肺活量'].astype(float)\n",
    "male['身高']=male['身高'].astype(float)\n",
    "male['体重']=male['体重'].astype(float)\n",
    "male['BMI']=male['BMI'].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "female['女50米跑']=female['女50米跑'].astype(float)\n",
    "female['女跳远']=female['女跳远'].astype(float)\n",
    "female['MV体前屈']=female['女体前屈'].astype(float)\n",
    "female['女仰卧']=female['女仰卧'].astype(float)\n",
    "female['女肺活量']=female['女肺活量'].astype(float)\n",
    "female['身高']=female['身高'].astype(float)\n",
    "female['体重']=female['体重'].astype(float)\n",
    "female['BMI']=female['BMI'].astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对体测成绩进行分数转换，跑步类（越小越好）；跳远、体前屈（越大越好）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>MV体前屈</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高    体重  BMI  MV体前屈  \\\n",
       "0   1  女    3.22   9.32  185.0    16  48.0  3775.0  163.0  51.3  0.0   16.0   \n",
       "1   1  女    4.59  11.44  148.0     9  29.0  3683.0  163.0  66.6  0.0    9.0   \n",
       "2   1  女    3.46  13.40  150.0     7  40.0  3331.0  157.0  60.0  0.0    7.0   \n",
       "3   1  女    3.39   9.52  172.0    21  46.0  3701.0  160.0  50.7  0.0   21.0   \n",
       "4   1  女    3.43   9.79  145.0     8  34.0  3592.0  167.0  63.9  0.0    8.0   \n",
       "\n",
       "   女800米跑分数  女50米跑分数  \n",
       "0     100.0     72.0  \n",
       "1      40.0     10.0  \n",
       "2      80.0      0.0  \n",
       "3      85.0     70.0  \n",
       "4      85.0     68.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['女800米跑','女50米跑']\n",
    "for col in cols:\n",
    "    def convert(x):\n",
    "        if x == 0: # 说明没有参加体能测试\n",
    "            return 0\n",
    "        for i in range(PE[col].shape[0]):\n",
    "            if x <= PE[col]['成绩'][i]:\n",
    "                return PE[col]['分数'][i]\n",
    "        return 0 # 说明跑的太慢了\n",
    "    female[col + '分数'] = female[col].map(convert)\n",
    "female.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>MV体前屈</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>女肺活量分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高    体重  BMI  MV体前屈  \\\n",
       "0   1  女    3.22   9.32  185.0    16  48.0  3775.0  163.0  51.3  0.0   16.0   \n",
       "1   1  女    4.59  11.44  148.0     9  29.0  3683.0  163.0  66.6  0.0    9.0   \n",
       "2   1  女    3.46  13.40  150.0     7  40.0  3331.0  157.0  60.0  0.0    7.0   \n",
       "3   1  女    3.39   9.52  172.0    21  46.0  3701.0  160.0  50.7  0.0   21.0   \n",
       "4   1  女    3.43   9.79  145.0     8  34.0  3592.0  167.0  63.9  0.0    8.0   \n",
       "\n",
       "   女800米跑分数  女50米跑分数  女跳远分数  女体前屈分数  女仰卧分数  女肺活量分数  \n",
       "0     100.0     72.0   85.0    76.0   85.0   100.0  \n",
       "1      40.0     10.0   60.0    66.0   66.0   100.0  \n",
       "2      80.0      0.0   60.0    64.0   76.0   100.0  \n",
       "3      85.0     70.0   76.0    90.0   85.0   100.0  \n",
       "4      85.0     68.0   50.0    64.0   70.0   100.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['女跳远', '女体前屈', '女仰卧', '女肺活量']\n",
    "\n",
    "for col in cols:\n",
    "    def convert(x):\n",
    "        if x == 0: # 说明没有参加体能测试\n",
    "            return 0\n",
    "        for i in range(PE[col].shape[0]):\n",
    "            if x >= PE[col]['成绩'][i]:\n",
    "                return PE[col]['分数'][i]\n",
    "        return 0 # 说明跑的太慢了\n",
    "    female[col + '分数'] = female[col].apply(convert)\n",
    "female.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['班级', '性别', '女800米跑','女800米跑分数',  '女50米跑','女50米跑分数', \n",
    "        '女跳远','女跳远分数', '女体前屈','女体前屈分数', '女仰卧','女仰卧分数', \n",
    "        '女肺活量', '女肺活量分数','身高','体重', 'BMI']\n",
    "female = female[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>女肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>100.0</td>\n",
       "      <td>9.32</td>\n",
       "      <td>72.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>16</td>\n",
       "      <td>76.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>19.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>40.0</td>\n",
       "      <td>11.44</td>\n",
       "      <td>10.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>9</td>\n",
       "      <td>66.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>25.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>80.0</td>\n",
       "      <td>13.40</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>7</td>\n",
       "      <td>64.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>24.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>85.0</td>\n",
       "      <td>9.52</td>\n",
       "      <td>70.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>21</td>\n",
       "      <td>90.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>19.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>85.0</td>\n",
       "      <td>9.79</td>\n",
       "      <td>68.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>8</td>\n",
       "      <td>64.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>22.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>78.0</td>\n",
       "      <td>9.60</td>\n",
       "      <td>70.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>24</td>\n",
       "      <td>95.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>2255.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>19.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>76.0</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>13</td>\n",
       "      <td>72.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>2937.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>21.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>80.0</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>15</td>\n",
       "      <td>76.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>2592.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>17.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>74.0</td>\n",
       "      <td>9.67</td>\n",
       "      <td>68.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>10</td>\n",
       "      <td>68.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>1829.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>18.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>50.0</td>\n",
       "      <td>9.09</td>\n",
       "      <td>74.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>10</td>\n",
       "      <td>68.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>2962.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.3</td>\n",
       "      <td>21.07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑  女800米跑分数  女50米跑  女50米跑分数    女跳远  女跳远分数  女体前屈  女体前屈分数  \\\n",
       "0     1  女    3.22     100.0   9.32     72.0  185.0   85.0    16    76.0   \n",
       "1     1  女    4.59      40.0  11.44     10.0  148.0   60.0     9    66.0   \n",
       "2     1  女    3.46      80.0  13.40      0.0  150.0   60.0     7    64.0   \n",
       "3     1  女    3.39      85.0   9.52     70.0  172.0   76.0    21    90.0   \n",
       "4     1  女    3.43      85.0   9.79     68.0  145.0   50.0     8    64.0   \n",
       "..   .. ..     ...       ...    ...      ...    ...    ...   ...     ...   \n",
       "588  17  女    3.51      78.0   9.60     70.0  150.0   60.0    24    95.0   \n",
       "589  17  女    4.00      76.0  10.18     64.0  150.0   60.0    13    72.0   \n",
       "590  17  女    3.45      80.0  10.18     64.0  152.0   62.0    15    76.0   \n",
       "591  17  女    4.01      74.0   9.67     68.0  165.0   70.0    10    68.0   \n",
       "592  17  女    4.48      50.0   9.09     74.0  180.0   80.0    10    68.0   \n",
       "\n",
       "      女仰卧  女仰卧分数    女肺活量  女肺活量分数     身高    体重    BMI  \n",
       "0    48.0   85.0  3775.0   100.0  163.0  51.3  19.31  \n",
       "1    29.0   66.0  3683.0   100.0  163.0  66.6  25.07  \n",
       "2    40.0   76.0  3331.0   100.0  157.0  60.0  24.34  \n",
       "3    46.0   85.0  3701.0   100.0  160.0  50.7  19.80  \n",
       "4    34.0   70.0  3592.0   100.0  167.0  63.9  22.91  \n",
       "..    ...    ...     ...     ...    ...   ...    ...  \n",
       "588  41.0   78.0  2255.0    70.0  158.0  49.0  19.63  \n",
       "589  36.0   72.0  2937.0    85.0  161.0  55.7  21.49  \n",
       "590  35.0   72.0  2592.0    76.0  165.0  48.6  17.85  \n",
       "591  41.0   78.0  1829.0    60.0  154.0  43.6  18.38  \n",
       "592  46.0   85.0  2962.0    85.0  162.0  55.3  21.07  \n",
       "\n",
       "[593 rows x 17 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = (female['体重']/(female['身高']/100)**2)\n",
    "female['BMI'] = s.fillna(0).round(2) # 保留两位小数\n",
    "female"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  男1000米跑  男50米跑    男跳远  男体前屈  男引体    男肺活量     身高    体重  BMI  \\\n",
       "0   1  男     4.13   8.88  195.0  12.0  1.0  2785.0  170.0  72.6  0.0   \n",
       "1   1  男     4.16   7.70  225.0  11.0  7.0  3133.0  174.0  52.7  0.0   \n",
       "2   1  男     4.09   8.45  218.0  14.0  1.0  3901.0  169.0  46.5  0.0   \n",
       "3   1  男     4.21   8.05  206.0  13.0  1.0  4946.0  183.0  79.7  0.0   \n",
       "4   1  男     3.44   7.52  210.0  13.0  9.0  3538.0  171.0  54.7  0.0   \n",
       "\n",
       "   男1000米跑分数  男50米跑分数  \n",
       "0       72.0     66.0  \n",
       "1       70.0     78.0  \n",
       "2       74.0     70.0  \n",
       "3       68.0     74.0  \n",
       "4       85.0     78.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['男1000米跑','男50米跑']\n",
    "for col in cols:\n",
    "    def convert(x):\n",
    "        if x == 0: # 说明没有参加体能测试\n",
    "            return 0\n",
    "        for i in range(PE[col].shape[0]):\n",
    "            if x <= PE[col]['成绩'][i]:\n",
    "                return PE[col]['分数'][i]\n",
    "        return 0 # 说明跑的太慢了\n",
    "    male[col + '分数'] = male[col].map(convert)\n",
    "male.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>男肺活量分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  男1000米跑  男50米跑    男跳远  男体前屈  男引体    男肺活量     身高    体重  BMI  \\\n",
       "0   1  男     4.13   8.88  195.0  12.0  1.0  2785.0  170.0  72.6  0.0   \n",
       "1   1  男     4.16   7.70  225.0  11.0  7.0  3133.0  174.0  52.7  0.0   \n",
       "2   1  男     4.09   8.45  218.0  14.0  1.0  3901.0  169.0  46.5  0.0   \n",
       "3   1  男     4.21   8.05  206.0  13.0  1.0  4946.0  183.0  79.7  0.0   \n",
       "4   1  男     3.44   7.52  210.0  13.0  9.0  3538.0  171.0  54.7  0.0   \n",
       "\n",
       "   男1000米跑分数  男50米跑分数  男跳远分数  男体前屈分数  男引体分数  男肺活量分数  \n",
       "0       72.0     66.0   60.0    74.0    0.0    62.0  \n",
       "1       70.0     78.0   74.0    74.0   60.0    68.0  \n",
       "2       74.0     70.0   70.0    78.0    0.0    80.0  \n",
       "3       68.0     74.0   64.0    76.0    0.0   100.0  \n",
       "4       85.0     78.0   66.0    76.0   68.0    74.0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['男跳远', '男体前屈', '男引体', '男肺活量']\n",
    "\n",
    "for col in cols:\n",
    "    def convert(x):\n",
    "        if x == 0: # 说明没有参加体能测试\n",
    "            return 0\n",
    "        for i in range(PE[col].shape[0]):\n",
    "            if x >= PE[col]['成绩'][i]:\n",
    "                return PE[col]['分数'][i]\n",
    "        return 0 # 说明跑的太慢了\n",
    "    male[col + '分数'] = male[col].apply(convert)\n",
    "male.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['班级', '性别', '男1000米跑','男1000米跑分数',  '男50米跑','男50米跑分数', \n",
    "        '男跳远','男跳远分数', '男体前屈','男体前屈分数', '男引体','男引体分数', \n",
    "        '男肺活量', '男肺活量分数','身高',\n",
    "       '体重', 'BMI']\n",
    "male= male[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>男肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>72.0</td>\n",
       "      <td>8.88</td>\n",
       "      <td>66.0</td>\n",
       "      <td>195.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>25.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>70.0</td>\n",
       "      <td>7.70</td>\n",
       "      <td>78.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>17.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>74.0</td>\n",
       "      <td>8.45</td>\n",
       "      <td>70.0</td>\n",
       "      <td>218.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>16.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>68.0</td>\n",
       "      <td>8.05</td>\n",
       "      <td>74.0</td>\n",
       "      <td>206.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>23.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>85.0</td>\n",
       "      <td>7.52</td>\n",
       "      <td>78.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>18.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.23</td>\n",
       "      <td>68.0</td>\n",
       "      <td>8.27</td>\n",
       "      <td>72.0</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4647.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>22.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.19</td>\n",
       "      <td>40.0</td>\n",
       "      <td>9.55</td>\n",
       "      <td>50.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>7042.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>24.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.25</td>\n",
       "      <td>100.0</td>\n",
       "      <td>7.50</td>\n",
       "      <td>80.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>5755.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>19.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.39</td>\n",
       "      <td>62.0</td>\n",
       "      <td>7.81</td>\n",
       "      <td>76.0</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>5688.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>17.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男1000米跑分数  男50米跑  男50米跑分数    男跳远  男跳远分数  男体前屈  男体前屈分数  \\\n",
       "0     1  男     4.13       72.0   8.88     66.0  195.0   60.0  12.0    74.0   \n",
       "1     1  男     4.16       70.0   7.70     78.0  225.0   74.0  11.0    74.0   \n",
       "2     1  男     4.09       74.0   8.45     70.0  218.0   70.0  14.0    78.0   \n",
       "3     1  男     4.21       68.0   8.05     74.0  206.0   64.0  13.0    76.0   \n",
       "4     1  男     3.44       85.0   7.52     78.0  210.0   66.0  13.0    76.0   \n",
       "..   .. ..      ...        ...    ...      ...    ...    ...   ...     ...   \n",
       "472  17  男     4.23       68.0   8.27     72.0  208.0   66.0  10.0    72.0   \n",
       "473  17  男     5.19       40.0   9.55     50.0  210.0   66.0  15.0    80.0   \n",
       "474  17  男     3.25      100.0   7.50     80.0  252.0   90.0  13.0    76.0   \n",
       "475  17  男     4.39       62.0   7.81     76.0  208.0   66.0  14.0    78.0   \n",
       "476  17  男      NaN        0.0   0.00      0.0    0.0    0.0   0.0     0.0   \n",
       "\n",
       "      男引体  男引体分数    男肺活量  男肺活量分数     身高    体重    BMI  \n",
       "0     1.0    0.0  2785.0    62.0  170.0  72.6  25.12  \n",
       "1     7.0   60.0  3133.0    68.0  174.0  52.7  17.41  \n",
       "2     1.0    0.0  3901.0    80.0  169.0  46.5  16.28  \n",
       "3     1.0    0.0  4946.0   100.0  183.0  79.7  23.80  \n",
       "4     9.0   68.0  3538.0    74.0  171.0  54.7  18.71  \n",
       "..    ...    ...     ...     ...    ...   ...    ...  \n",
       "472   0.0    0.0  4647.0   100.0  176.0  69.5  22.44  \n",
       "473   6.0   50.0  7042.0   100.0  177.0  76.0  24.26  \n",
       "474  13.0   85.0  5755.0   100.0  181.0  65.0  19.84  \n",
       "475  11.0   76.0  5688.0   100.0  172.0  51.7  17.48  \n",
       "476   0.0    0.0     0.0     0.0    0.0   0.0   0.00  \n",
       "\n",
       "[477 rows x 17 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = (male['体重']/(male['身高']/100)**2)\n",
    "male['BMI'] = s.fillna(0).round(2) # 保留两位小数\n",
    "male"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
